package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import helperclasses.AlterLog;
import helperclasses.DbUtil;

public class AlterLogSave {
	AlterLog alterLog;

	public AlterLogSave(AlterLog alterLog) {
		this.alterLog = alterLog;
	}

	private Connection getConnection() throws SQLException {
		DataSource ds = DbUtil.getInstance().getDataSource();
		Connection conn = ds.getConnection();
		return conn;
	}

	public void saveData() throws SQLException, ParseException {
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql;
		java.text.DecimalFormat df = new DecimalFormat("########,####");
		double valoare = 0, cantitateVeche = 0, valoareInStocVeche = 0, diferentaCantitati = 0, valoareInStocNoua = 0;
		int tipNir = 0;
		try {
			conn.setAutoCommit(false);
			// insert data in alterlog table
			sql = "INSERT INTO alterlogs (alterlogs_id, produs_id_alterlogs, valoare, alter_date, observatii, userName) VALUES( null, "
					+ alterLog.getProdusId()
					+ ", "
					+ alterLog.getValue()
					+ ", '"
					+ alterLog.getAlterDate()
					+ "', '"
					+ alterLog.getOvservatii() + "', '" + alterLog.getUser() + "')";
			st.executeUpdate(sql);
			
			sql = "SELECT prod.valoare, prod.tip_nir, ps.valoare_in_stoc, ps.cantitate FROM produse prod NATURAL JOIN produse_in_stoc ps WHERE produs_id="
					+ alterLog.getProdusId();
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				valoare = Double.parseDouble(result.getString("valoare"));
				tipNir = Integer.parseInt(result.getString("tip_nir"));
				cantitateVeche = Double.parseDouble(result.getString("cantitate"));
				valoareInStocVeche = Double.parseDouble(result.getString("valoare_in_stoc"));
			}

			if (tipNir == 1 || tipNir == 2 || tipNir == 4) {				
				if (cantitateVeche != alterLog.getValue()) {
					diferentaCantitati = valoareInStocVeche - (cantitateVeche * valoare);
					diferentaCantitati = (double) Math.round(diferentaCantitati * 10000) / 10000;
					valoareInStocNoua = ((alterLog.getValue() + cantitateVeche) * valoare) + diferentaCantitati;
				}

				// Update table Produse In Stoc
				sql = "UPDATE produse_in_stoc SET cantitate=cantitate+" + alterLog.getValue() + ", valoare_in_stoc="
						+ valoareInStocNoua + " WHERE produs_id=" + alterLog.getProdusId();
				st.executeUpdate(sql);
			} else if (tipNir == 3) {
				// this is a composed product, we have to alter each component				
				// 1. we must get the ingredients IDsfrom the DB
				sql = "SELECT produs_id_retetar, valoare FROM retetar WHERE cocktail_id=" + alterLog.getProdusId();
				result = st.executeQuery(sql);
				List<Integer> ingredientId = new ArrayList<Integer>();
				List<Double> valoareIngredientRetetar = new ArrayList<Double>();
				while (result.next()) {
					ingredientId.add(Integer.parseInt(result.getString("produs_id_retetar")));
					valoareIngredientRetetar.add(Double.parseDouble(result.getString("valoare")));
				}

				for (int i = 0; i < ingredientId.size(); i++) {
					// 2. we go trough each ingredient and we alter
					// valoare_in_stoc
					// and cantitate fields according with the sales
					sql = "SELECT prod.valoare, ps.cantitate, ps.valoare_in_stoc FROM produse prod NATURAL JOIN produse_in_stoc ps WHERE produs_id="
							+ ingredientId.get(i);
					result = st.executeQuery(sql);
					result.next();
					double newValoareInStoc = 0;
					double oldValoareInStoc = Double.parseDouble(result.getString("valoare_in_stoc"));
					int cantitate = Integer.parseInt(result.getString("cantitate"));
					double valoare1 = Double.parseDouble(result.getString("valoare"));
					double valoareVanduta = 0;
					int cantitateNoua = 0;

					valoareVanduta = Math.round((valoareIngredientRetetar.get(i) * alterLog.getValue()) * 10000) / 10000.0;
					newValoareInStoc = Math.round((oldValoareInStoc + valoareVanduta) * 10000) / 10000.0;
					cantitateNoua = (int) (newValoareInStoc / valoare1);

					sql = "UPDATE produse_in_stoc SET cantitate=" + cantitateNoua + ", valoare_in_stoc=" + newValoareInStoc
							+ " WHERE produs_id=" + ingredientId.get(i);
					st.executeUpdate(sql);
				}
			}
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}

	}

}
